package com.share.common.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.share.common.constant.DirConstant;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

@Slf4j
public class SqliteUtil {
    private final static String NUL = "NULL";
    public static int put(String key, Object obj) {
        return put(key, obj, null);
    }

    public static int put(String key, Object obj, Integer expire) {
        if(StringUtils.isEmpty(key)){
            key  = NUL;
        }
        String value = JSON.toJSONString(obj);
        return SqliteDb.put(key, value, expire);
    }

    public static String get(String key) {
        if(StringUtils.isEmpty(key)){
            key = NUL;
        }
        return SqliteDb.get(key);
    }

    public static boolean exist(String key) {
        if(StringUtils.isEmpty(key)){
            key = NUL;
        }
        return SqliteDb.exist(key);
    }

    public static int del(String key) {
        if(StringUtils.isEmpty(key)){
            key = NUL;
        }
        return SqliteDb.del(key);
    }

    public static int updateExpires(String key, Integer expire) {
        if(StringUtils.isEmpty(key)){
            key = NUL;
        }
        return SqliteDb.update(key, expire);
    }

    public static void main(String[] args) {
        String s = get("212");
        System.out.println(s);
    }

    static class SqliteDb {
        private static String dbDir = new File(DirConstant.USER_HOME).getAbsolutePath();
        private static String dbFile = dbDir + File.separator + "sqlitecache.db";
        private static String dbUrl = "jdbc:sqlite:" + dbFile;

        private static List<Connection> pool = new LinkedList<>();

        static {
            if (!new File(dbFile).exists()) {
                log.info("正在初始化数据库,路径为" + dbUrl);
                initDb();
            }

            /*1秒 =1000 毫秒 ；1 毫秒 = 1000微秒；1微秒 = 1000纳秒；1纳秒 = 1000皮秒。*/
            int haomiao = 1000 * 10;
            new Timer().schedule(new TimerTask() {
                @Override
                public void run() {
                    Thread thread = Thread.currentThread();
                    thread.setName("sqlite线程");
                    DelayUtil.sleepMillis(haomiao/2);
                    int count = delExpire();
                    if (count > 0) {
                        log.info("sqlite清理过期数据条数:" + count);
                    }
                }
            }, new Date(), haomiao);
        }

        public static Connection getConnection() {
            if (pool.size() > 0) {
                return pool.get(0);
            } else {
                try {
                    Class.forName("org.sqlite.JDBC");
                    Connection connection = DriverManager.getConnection(dbUrl);
                    pool.add(connection);
                    return connection;
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }
        }

        /*查询*/
        public static JSONArray executeQuery(Connection connection, String sql, Object... args) {
            try {
                PreparedStatement pstat = connection.prepareStatement(sql);

                if (args.length > 0) {
                    for (int i = 0, len = args.length; i < len; i++) {
                        pstat.setObject(i + 1, args[i]);
                    }
                }

                ResultSet rst = pstat.executeQuery();
                return DbUtil.convert(rst);

            } catch (Exception throwables) {
                throwables.printStackTrace();
                return null;
            }
        }

        /*增删改*/
        public static int executeUpdate(Connection connection, String sql) {
            int count = 0;
            try {
                PreparedStatement pstat = connection.prepareStatement(sql);
                count = pstat.executeUpdate();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
            return count;
        }

        public static int put(String key, String value, Integer expire) {
            Long ctime = System.currentTimeMillis() / 1000;
            String sql = "INSERT INTO \"ts_cache\" (\"key\", \"value\", \"ctime\", \"expire\") VALUES ('${key}', '${value}', '${ctime}', '${expire}');";
            sql = sql.replace("${key}", key);
            sql = sql.replace("${value}", value);
            sql = sql.replace("${ctime}", ctime + "");
            if (expire != null) {
                sql = sql.replace("${expire}", expire + "");
            } else {
                sql = sql.replace("${expire}", "NULL");
            }
            return executeUpdate(getConnection(), sql);
        }

        public static String get(String key) {
            String sql = "SELECT value from ts_cache where key='${key}'";
            sql = sql.replace("${key}", key);
            JSONArray objects = executeQuery(getConnection(), sql);
            if (objects != null && objects.size() == 1) {
                JSONObject obj = (JSONObject) objects.get(0);
                return obj.getString("value");
            } else {
                return null;
            }
        }

        public static boolean exist(String key) {
            String sql = "SELECT count(1) from ts_cache where key='${key}'";
            sql = sql.replace("${key}", key);
            JSONArray objects = executeQuery(getConnection(), sql);
            if (objects != null && objects.size() == 1) {
                JSONObject obj = (JSONObject) objects.get(0);
                return obj.getInteger("count(1)") > 0;
            } else {
                return false;
            }
        }

        public static int del(String key) {
            String sql = "DELETE from ts_cache where  key='${key}'";
            sql = sql.replace("${key}", key);
            return executeUpdate(getConnection(), sql);
        }

        public static int update(String key, Integer expire) {
            Long ctime = System.currentTimeMillis() / 1000;
            String sql = "UPDATE ts_cache SET ctime='${ctime}',expire='${expire}' where key='${key}'";
            sql = sql.replace("${key}", key);
            sql = sql.replace("${ctime}", ctime + "");
            if (expire != null) {
                sql = sql.replace("${expire}", expire + "");
            } else {
                sql = sql.replace("${expire}", "NULL");
            }
            return executeUpdate(getConnection(), sql);
        }

        public static int initDb() {
            String sql = "CREATE TABLE \"ts_cache\" (\n" +
                    "\"key\"  TEXT NOT NULL,\n" +
                    "\"value\"  TEXT,\n" +
                    "\"ctime\"  INTEGER NOT NULL,\n" +
                    "\"expire\"  INTEGER,\n" +
                    "PRIMARY KEY (\"key\")\n" +
                    ");\n" +
                    "CREATE UNIQUE INDEX \"key\" ON \"ts_cache\" (\"key\" ASC);";
            return executeUpdate(getConnection(), sql);
        }

        public static int delExpire() {
            Long time = System.currentTimeMillis() / 1000;
            String sql = "DELETE from ts_cache where expire IS NOT NULL and  ctime + expire < " + time;
            return executeUpdate(getConnection(), sql);
        }
    }
}
